﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

using Excel = Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.Data;
using System.IO;
using RegisterationCommon.Common;
namespace RegisterationCommon.Education
{
   public class EduExcelHelper
    {
        Excel.Application app;
        Excel._Workbook wbook;
        Excel._Worksheet oSheet;
        public void CreateDirectory(string path)
        {
            if (!Directory.Exists(path))
            {
                Directory.CreateDirectory(path);
            }
        }
        public string SCexcel(DataTable dt, string pathLong)
        {
            //打开excel文档
            app = new Excel.Application();//添加一个 Excle应用对象
            wbook = app.Workbooks.Add(true);//创建excel工作薄 
            oSheet = (Excel._Worksheet)wbook.Worksheets[1];//创建一张sheet表
            oSheet.Name = "学生登记信息";
            //定义文件保存路径
            string filename1 = "StudentInfo_" + DateTime.Now.ToString("yyyyMMdd_hhmmss") + ".xls";//因为保存的不平凡，所以之精确到年和月                  。否则就保存到毫秒
            //string filename2 = pathLong + "\\" + filename1;//保存在服务器的路径
            CreateDirectory(pathLong);
            string filename2 = pathLong + filename1;//保存在服务器的路径
            addExecl(filename2.ToString(), dt);//内部的一个方法，把服务器路径与之前的数据放入addExcle()中，目的是在把数据放入Excel中

            //打开后就要关闭。O(∩_∩)O~

            app.Workbooks.Close();
            //同样不要忘记结束进程
            System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);
            System.Runtime.InteropServices.Marshal.ReleaseComObject(app);

            GC.Collect();//强制对所有代进行即时垃圾回收
            return filename1;
        }
        //第二个方法addExecl();我们来看看他是如何处理数据的，并且使用你自己想要的格式来定义Excel
        private void addExecl(string xlsPath, DataTable dt)
        {
            Excel.Range oRng;
            Excel.Range range;
            //标题
            int excel_cur = 1;
            oSheet.Cells[excel_cur, 1] = "学生登记信息";
            excel_cur++;

            //字段名        
            oSheet.Cells[excel_cur, 1] = "所属学校 ";
            oSheet.Cells[excel_cur, 2] = "姓名";
            oSheet.Cells[excel_cur, 3] = "性别";
            oSheet.Cells[excel_cur, 4] = "证件号(身份证/护照)";
            oSheet.Cells[excel_cur, 5] = "出生年月";
            oSheet.Cells[excel_cur, 6] = "户口所在地";
            oSheet.Cells[excel_cur, 7] = "住房社区";
            oSheet.Cells[excel_cur, 8] = "住房小区";
            oSheet.Cells[excel_cur, 9] = "门牌号";
            oSheet.Cells[excel_cur, 10] = "房产证号";
            oSheet.Cells[excel_cur, 11] = "户口迁入时间";

            oSheet.Cells[excel_cur, 12] = "住房入住时间";
            oSheet.Cells[excel_cur, 13] = "户籍地址";
            oSheet.Cells[excel_cur, 14] = "第一监护人";
            oSheet.Cells[excel_cur, 15] = "与学生关系";
            oSheet.Cells[excel_cur, 16] = "工作单位";
            oSheet.Cells[excel_cur, 17] = "联系电话";
            oSheet.Cells[excel_cur, 18] = "第二监护人";
            oSheet.Cells[excel_cur, 19] = "与学生关系";
            oSheet.Cells[excel_cur, 20] = "工作单位";
            oSheet.Cells[excel_cur, 21] = "联系电话";
            excel_cur++;

            //行数据绑定
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataTable schoolname = DBAction.GetDTFromSQL("Select [Name] From " + ETableName.SchoolInfo + " where ID=" + dt.Rows[i]["SchID"].ToString());
                    if (schoolname.Rows.Count > 0)
                    {
                        oSheet.Cells[excel_cur, 1] = schoolname.Rows[0]["Name"].ToString();
                    }
                    else
                    {
                        oSheet.Cells[excel_cur, 1] ="";
                    }
                    oSheet.Cells[excel_cur, 2] = dt.Rows[i]["TrueName"].ToString();
                    if (bool.Parse(dt.Rows[i]["Sex"].ToString()) == true)
                    {
                        oSheet.Cells[excel_cur, 3] = "女";
                    }
                    else
                    {
                        oSheet.Cells[excel_cur, 3] = "男";
                    }
                    oSheet.Cells[excel_cur, 4] ="'"+dt.Rows[i]["IDCardNumber"].ToString();
                    oSheet.Cells[excel_cur, 5] = DateTime.Parse(dt.Rows[i]["Birthday"].ToString()).ToString("yyyyMMdd");
                    if (dt.Rows[i]["HouseAreaID"].ToString() == "0")
                    {
                        oSheet.Cells[excel_cur, 6] = "经济技术开发区";
                    }
                    else if (dt.Rows[i]["HouseAreaID"].ToString() == "1")
                    {
                        oSheet.Cells[excel_cur, 6] = "市内其它区";
                    }
                    else if (dt.Rows[i]["HouseAreaID"].ToString() == "2")
                    {
                        oSheet.Cells[excel_cur, 6] = "省内市外、外省外市";
                    }
                    else
                    {
                        oSheet.Cells[excel_cur, 6] = "外籍(含港澳台)";
                    }
                    oSheet.Cells[excel_cur, 7] = dt.Rows[i]["ScommName"].ToString();
                    oSheet.Cells[excel_cur, 8] = dt.Rows[i]["XcommName"].ToString();
                    oSheet.Cells[excel_cur, 9] = dt.Rows[i]["HouseNumber"].ToString();
                    oSheet.Cells[excel_cur, 10] = dt.Rows[i]["HouseCard"].ToString();
                    oSheet.Cells[excel_cur, 11] = dt.Rows[i]["HouseIngoingTime"].ToString();

                    oSheet.Cells[excel_cur, 12] = dt.Rows[i]["HousingOccupancyTime"].ToString();
                    oSheet.Cells[excel_cur, 13] = dt.Rows[i]["PermanentAddress"].ToString();
                    oSheet.Cells[excel_cur, 14] = dt.Rows[i]["FirstGuardianName"].ToString();
                    oSheet.Cells[excel_cur, 15] = dt.Rows[i]["FirstRelationship"].ToString();
                    oSheet.Cells[excel_cur, 16] = dt.Rows[i]["FirstGuardianUnit"].ToString();
                    oSheet.Cells[excel_cur, 17] = dt.Rows[i]["FirstGuardianPhone"].ToString();
                    oSheet.Cells[excel_cur, 18] = dt.Rows[i]["SecGuardianName"].ToString();
                    oSheet.Cells[excel_cur, 19] = dt.Rows[i]["SecRelationship"].ToString();
                    oSheet.Cells[excel_cur, 20] = dt.Rows[i]["SecGuardianUnit"].ToString();
                    oSheet.Cells[excel_cur, 21] = dt.Rows[i]["SecGuardianPhone"].ToString();
                    excel_cur++;

                }
            }
            //格式定义
            range = (Excel.Range)oSheet.get_Range("A1", "J1");//选中 A1:J1 单元格
            range.Merge(0);//合并单元格
            range.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//纵向居中 
            range.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;//横向居中
            range.Font.Size = 16;
            // range.Font.Name = "黑体";
            range.RowHeight = 24;

            oRng = oSheet.get_Range("A2", "T" + Convert.ToString(dt.Rows.Count + 2));
            oRng.Borders.LineStyle = 0;
            oRng.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter;//居中
            oRng.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter;
            oRng.EntireColumn.AutoFit();//列宽自动  
            oRng.NumberFormatLocal = "@";

            oSheet.get_Range((Excel.Range)oSheet.Cells[2, 1], (Excel.Range)oSheet.Cells[2, 21]).Select();
            oSheet.get_Range((Excel.Range)oSheet.Cells[2, 1], (Excel.Range)oSheet.Cells[2, 21]).Interior.ColorIndex =33; 

            app.Application.DisplayAlerts = false;
            oSheet.SaveAs(xlsPath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);//文件保存

        }


        #region 获取教育局状态学生列表
        /// <summary>
        /// 获取教育局状态学生列表
        /// </summary>
        /// <param name="context"></param>
       public DataTable GetUndistributedStuList(string sqlwhere,string typeid)
        {
            //页面查询条件
            string where = sqlwhere;
            if (where != "1=1")
            {
                string[] SelList = where.Split(new char[] { ',' }, StringSplitOptions.None);
                where = " 1=1 ";
                if (SelList[0] != "")
                {
                    where += " and TrueName like '%" + SelList[0] + "%'";//学生姓名
                }
                if (SelList[1] != "")
                {
                    where += " and IDCardNumber like '%" + SelList[1] + "%'";//学生身份证
                }
                if (SelList[2] != "99")
                {
                    where += " and Sex =" + SelList[2] + "";//性别
                }
                if (SelList[3] != "99")
                {
                    where += " and SchID =" + SelList[3] + "";//所属学校
                }
                if (SelList[4] != "99")
                {
                    where += " and HouseAreaID =" + SelList[4] + "";//户口所在地
                }
                if (SelList[5] != "999" && SelList[5] != "")
                {
                    where += " and ScommAreaID =" + SelList[5] + "";//住房社区
                }
                if (SelList[6] != "" && SelList[6] != "999")
                {
                    where += " and XcommAreaID like '%" + SelList[6] + "%'";//住房小区
                }
                if (SelList[7] != "99")
                {
                    where += " and Level = " + SelList[7] + "";//学生等级
                }
                if (SelList[8] != "" && SelList[9] != "")
                {
                    where += " and Birthday between '" + SelList[8] + "' and '" + SelList[9] + "'";//出生日月
                }
            }
            //初始查询条件
            string whereList = "";
            string typeID = typeid;
            switch (typeID)
            {
                //待分配学生信息
                case "0":
                    whereList = " IsMatured =1  and Status=0 and SchID=0 and IsDelete=0 and HouseAreaID!=1 and " + where;
                    break;
                //已分配学生信息
                case "1":
                    whereList = " IsMatured =1  and Status=1 and SchID>0 and IsDelete=0 and " + where;
                    break;
                //待审核学生信息
                case "2":
                    whereList = " IsMatured =1  and Status=1 and SchID>0 and IsDelete=0 and " + where;
                    break;
                //已审核学生信息
                case "3":
                    whereList = "  IsMatured =1  and Status=2 and SchID>0 and IsDelete=0 and " + where;
                    break;
                //已删除学生信息
                case "4":
                    whereList = " IsDelete=1 and " + where;
                    break;
                //不足龄学生信息
                case "5":
                    whereList = " IsMatured =0 and " + where;
                    break;
                //特招学生信息
                case "6":
                    whereList = " IsMatured =1  and Status=2 and SchID>0 and IsDelete=0 and Level=3 and " + where;
                    break;
                //被放弃学生信息
                case "7":
                    whereList = " IsMatured =1 and Status=3 and IsDelete=0 and " + where;
                    break;
                //市内其它区且无房产学生信息
                case "8":
                    whereList = " IsMatured =1 and Status=0 and SchID=0 and HouseAreaID=1 and IsDelete=0 and " + where;
                    break;
                default:
                    break;
            }
            DataTable totaldt = DBAction.GetDTFromSQL("select * from Students where " + whereList);
            return totaldt;
        }
        #endregion

    }
}
